package com.ruoyi.ruoyictmanage.mapper;

import com.ruoyi.common.core.domain.entity.SysDept;
import com.ruoyi.ruoyictmanage.domain.entity.CT;
import com.ruoyi.ruoyictmanage.domain.entity.Line;
import com.ruoyi.ruoyictmanage.domain.entity.MPN;
import com.ruoyi.ruoyictmanage.domain.entity.MPNCTDTO;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface MPNMapper {


    @Select("select * from mpn")
    @Results(id="MPNResultMap",value = {
            @Result(property = "line", column = "line_id", javaType = Line.class ,one = @One(select = "com.ruoyi.ruoyictmanage.mapper.LineMapper.selectLineById"))
    })
    List<MPN> getMPNlist();

    @Insert("insert into mpn(MPN_name,internal_name,project_name,process_name,insert_date,line_id,benchmark_value,process,IE,ic_name,DFF,psc_sheet,device_config,state,dept_name,class_name,floor,bu)" +
            " values(#{MPN_name},#{internal_name},#{project_name},#{process_name},now(),#{lineId},#{benchmark_value},#{process},#{IE},#{ic_name},#{DFF},#{psc_sheet},#{device_config},1,#{dept_name},#{class_name},#{floor},#{bu}")
    int addMPN(MPN mpn);

    @Update("UPDATE mpn \n" +
            "SET \n" +
            "    MPN_name = #{MPN_name},\n" +
            "    internal_name = #{internal_name},\n" +
            "    project_name = #{project_name},\n" +
            "    process_name = #{process_name},\n" +
            "    line_id = #{lineId},\n" +
            "    benchmark_value = #{benchmark_value},\n" +
            "    process = #{process},\n" +
            "    IE = #{IE},\n" +
            "    ic_name = #{ic_name},\n" +
            "    DFF = #{DFF},\n" +
            "    psc_sheet = #{psc_sheet},\n" +
            "    device_config = #{device_config},\n" +
            "    dept_name = #{dept_name},\n" +
            "    class_name = #{class_name},\n" +
            "    floor = #{floor},\n" +
            "    bu = #{bu}\n" +
            "WHERE MPN_id = #{MPN_id};")
    int setMPN(MPN mpn);

    @Select("<script>" +
            "SELECT m.MPN_id, m.MPN_name, m.internal_name, project_name, m.process_name, m.line_id, " +
            "COALESCE(c.CT_id, '') CT_id, COALESCE(c.benchmark_value, m.benchmark_value) benchmark_value, " +
            "COALESCE(enter_data, '') enter_data, COALESCE(test_values, '') test_values, " +
            "COALESCE(exception_cause, '') exception_cause, COALESCE(countermeasure, '') countermeasure, " +
            "COALESCE(acceptance, '') acceptance, COALESCE(NPI, '') NPI, " +
            "COALESCE(c.process, m.process) process, COALESCE(c.IE, m.IE) IE, " +
            "COALESCE(c.ic_name, m.ic_name) ic_name, COALESCE(c.DFF, m.DFF) DFF, " +
            "COALESCE(c.psc_sheet, m.psc_sheet) psc_sheet, COALESCE(c.device_config, m.device_config) device_config, " +
            "m.state, COALESCE(c.dept_name, m.dept_name) dept_name, COALESCE(c.class_name, m.class_name) class_name, " +
            "COALESCE(c.floor, m.floor) floor, COALESCE(c.bu, m.bu) BU " +
            "FROM mpn m " +
            "LEFT JOIN ct c ON m.MPN_id = c.MPN_id " +
            "<choose>" +
            "    <when test=\"timeStar != null and timeStar != '' and timeEnd != null and timeEnd != ''\">" +
            "        AND enter_data BETWEEN #{timeStar} AND #{timeEnd}" +
            "    </when>" +
            "    <otherwise>" +
            "        AND enter_data = CURDATE()" +
            "    </otherwise>" +
            "</choose>" +
            "WHERE m.state=1" +
            "<if test=\"line != null and line.size() > 0\">" +
            "    AND m.line_id IN " +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>" +
            "<if test=\"mpnName != null and mpnName != ''\">" +
            "    AND m.MPN_name LIKE CONCAT('%', #{mpnName}, '%')" +
            "</if>" +
            "<if test=\"ie != null and ie != ''\">" +
            "    AND m.IE LIKE CONCAT('%', #{ie}, '%')" +
            "</if>" +
            "</script>")
    @Results(id="MPNCTResultMap", value = {
            @Result(property = "line", column = "line_id", javaType = Line.class,
                    one = @One(select = "com.ruoyi.ruoyictmanage.mapper.LineMapper.selectLineById"))
    })
    List<MPNCTDTO> getCTTableByLineIdEnterDateMPNName(
            @Param("line") List<Integer> line,
            @Param("timeStar") String timeStar,
            @Param("timeEnd") String timeEnd,
            @Param("mpnName") String mpnName,
            @Param("ie") String ie);


    @Select("<script>" +
            "SELECT * FROM mpn " +
            "<where>" +
            "1=1"+
            "   <if test='mpnName != null and mpnName != \"\"'> and MPN_name like CONCAT('%',#{mpnName},'%') </if>" +
            "   <if test='lineId >=0 '> and line_Id =#{lineId} </if>"+ // 区分大小写
            "   <if test='state >=0 '> and state =#{state} </if>"+
            "   <if test='timeStar != \"\" and timeEnd != \"\"'> and insert_date between #{timeStar} and #{timeEnd} </if>" +
            "   <if test='ie != \"\" and ie != \"\"'> and IE like CONCAT('%',#{ie},'%')  </if>" +
            "</where>" +
            "</script>")
    @ResultMap("MPNResultMap")
    List<MPN> getMPNlistByQueryParams(@Param("mpnName")String mpnName,@Param("lineId")int lineId,@Param("state")int state,@Param("timeStar")String timeStar,@Param("timeEnd")String timeEnd,@Param("ie")String ie);


    @Insert("<script>" +
            "INSERT INTO mpn(" +
            "MPN_id, MPN_name, internal_name, project_name, process_name, " +
            "insert_date, line_id, benchmark_value, process, IE, ic_name, " +
            "DFF, psc_sheet, device_config, state, dept_name, class_name, " +
            "floor, bu" +
            ") VALUES " +
            "<foreach collection='dataList' item='item' separator=','>" +
            "(#{item.MPN_id}, #{item.MPN_name}, #{item.internal_name}, #{item.project_name}, #{item.process_name}, " +
            "NOW(), #{item.lineId}, #{item.benchmark_value}, #{item.process}, " +
            "#{item.IE}, #{item.ic_name}, #{item.DFF}, #{item.psc_sheet}, #{item.device_config}, " +
            "1, #{item.dept_name}, #{item.class_name}, #{item.floor}, #{item.bu})" +
            "</foreach>" +
            " ON DUPLICATE KEY UPDATE " +
            "MPN_name = VALUES(MPN_name), " +
            "internal_name = VALUES(internal_name), " +
            "project_name = VALUES(project_name), " +
            "process_name = VALUES(process_name), " +
            "insert_date = NOW(), " +
            "line_id = VALUES(line_id), " +
            "benchmark_value = VALUES(benchmark_value), " +
            "process = VALUES(process), " +
            "IE = VALUES(IE), " +
            "ic_name = VALUES(ic_name), " +
            "DFF = VALUES(DFF), " +
            "psc_sheet = VALUES(psc_sheet), " +
            "device_config = VALUES(device_config), " +
            "state = VALUES(state), " +
            "dept_name = VALUES(dept_name), " +
            "class_name = VALUES(class_name), " +
            "floor = VALUES(floor), " +
            "bu = VALUES(bu)" +
            "</script>")
    void batchSave(@Param("dataList") List<MPN> dataList);

    @Update("<script>" +
            "UPDATE mpn SET state = 1 - #{state}" +
            "<choose>" +
            "   <when test='state == 1'>, deprecated_date = now()</when>" +
            "   <when test='state == 0'>, deprecated_date = null</when>" +
            "</choose>" +
            " WHERE MPN_id = #{id}" +
            "</script>")
    Long setMpnState(@Param("id") Long id, @Param("state") int state);

    @Insert("<script>" +
            "insert into ct(benchmark_value,enter_data,test_values,MPN_id,exception_cause,countermeasure" +
            "<if test='acceptance != null and acceptance != \"\"'>,acceptance</if>" +
            ",NPI,process,IE,ic_name,DFF,psc_sheet,device_config,dept_name,class_name,floor,bu)" +
            "values(#{benchmark_value},#{enter_data},#{test_values},#{MPN_id},#{exception_cause},#{countermeasure}" +
            "<if test='acceptance != null and acceptance != \"\"'>,#{acceptance}</if>" +
            ",#{NPI},#{process},#{IE},#{ic_name},#{DFF},#{psc_sheet},#{device_config},#{dept_name},#{class_name},#{floor},#{bu})" +
            "</script>")
    int setAddCTtable(CT ct);

    @Update("<script>" +
            "UPDATE ct SET " +
            "    benchmark_value = #{benchmark_value},\n" +
            "    enter_data = #{enter_data},\n" +
            "    test_values = #{test_values},\n" +
            "    MPN_id = #{MPN_id},\n" +
            "    exception_cause = #{exception_cause},\n" +
            "    countermeasure = #{countermeasure},\n" +
            "<if test='acceptance != \"\" and acceptance !=null '>acceptance = #{acceptance},</if>"+
            "<if test='NPI != \"\" and NPI !=null '>NPI = #{NPI},</if>"+
            "    process = #{process},\n" +
            "    IE = #{IE},\n" +
            "    ic_name = #{ic_name},\n" +
            "    DFF = #{DFF},\n" +
            "    psc_sheet = #{psc_sheet},\n" +
            "    device_config = #{device_config},\n" +
            "    dept_name = #{dept_name},\n" +
            "    class_name = #{class_name},\n" +
            "    floor = #{floor},\n" +
            "    bu = #{bu}\n" +
            "WHERE CT_id = #{CT_id}"+
            "</script>")
    int setUpdateCTtable(CT ct);

    @Insert("<script>" +
            "INSERT INTO ct (" +
            "benchmark_value, enter_data, test_values, MPN_id, exception_cause, " +
            "countermeasure, acceptance, process, NPI, IE, ic_name, DFF, " +
            "psc_sheet, device_config, dept_name, class_name, floor, bu" +
            ") VALUES " +
            "<foreach collection='dataList' item='item' separator=','>" +
            "(" +
            "#{item.benchmark_value}, #{item.enter_data}, #{item.test_values}, #{item.MPN_id}, " +
            "#{item.exception_cause}, #{item.countermeasure}, #{item.acceptance}, " +
            "<choose>" +
            "  <when test='item.process != null and item.process != \"\"'>#{item.process}</when>" +
            "  <otherwise>now()</otherwise>" +
            "</choose>, " +
            "#{item.NPI}, #{item.IE}, #{item.ic_name}, #{item.DFF}, " +
            "#{item.psc_sheet}, #{item.device_config}, #{item.dept_name}, " +
            "#{item.class_name}, #{item.floor}, #{item.bu}" +
            ")" +
            "</foreach>" +
            "</script>")
    void batchSaveCT(@Param("dataList") List<CT> dataList);

    @Delete("DELETE from ct where ct_id=#{id}")
    int delct(@Param("id") Long id);


    @Results(id="MPNCTDBMAP", value = {
            @Result(property = "line", column = "line_id", javaType = Line.class,
                    one = @One(select = "com.ruoyi.ruoyictmanage.mapper.LineMapper.selectLineById"))
    })
    @Select("<script>" +
            "\n" +
            "select c.CT_id,m.MPN_name,c.MPN_id, \n" +
            "    m.internal_name, \n" +
            "    project_name, \n" +
            "    m.process_name,c.enter_data,\n" +
            "    m.line_id,c.benchmark_value,c.test_values,c.exception_cause,c.countermeasure,c.acceptance,c.NPI  from ct c inner join mpn m on c.MPN_id=m.MPN_id"+
            " WHERE 1=1" +
            "<choose>" +
            "    <when test=\"timeStar != null and timeStar != '' and  timeStar != 'null' and timeEnd != null and timeEnd != '' and  timeEnd != 'null'\">" +
            "        AND enter_data BETWEEN #{timeStar} AND #{timeEnd}" +
            "    </when>" +
            "    <otherwise>" +
            "        AND YEAR(enter_data) = YEAR(CURRENT_DATE)\n" +
            "    AND MONTH(enter_data) = MONTH(CURRENT_DATE)" +
            "    </otherwise>" +
            "</choose>" +
            "<if test=\"line != null and line.size() > 0\">" +
            "    AND m.line_id IN " +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>" +
            "<if test=\"mpnName != null and mpnName != ''\">" +
            "    AND m.MPN_name LIKE CONCAT('%', #{mpnName}, '%')" +
            "</if>" +
            "<if test='state != 2'>" +
            "    AND m.state = #{state}" +
            "</if>" +
            " order by enter_data"+
            "</script>")
    List<MPNCTDTO> getCTTableDBByLineIdEnterDateMPNName(@Param("line") List<Integer> line, @Param("timeStar") String timeStar,@Param("timeEnd") String timeEnd,@Param("mpnName") String mpnName,@Param("state") int state);


    @ResultMap(value = "MPNCTResultMap")
    @Select("<script>" +
            "SELECT m.MPN_id, m.MPN_name, m.internal_name, project_name, m.process_name, m.line_id, " +
            "COALESCE(c.CT_id, '') CT_id, COALESCE(c.benchmark_value, m.benchmark_value) benchmark_value, " +
            "COALESCE(enter_data, '') enter_data, COALESCE(test_values, '') test_values, " +
            "COALESCE(exception_cause, '') exception_cause, COALESCE(countermeasure, '') countermeasure, " +
            "COALESCE(acceptance, '') acceptance, COALESCE(NPI, '') NPI, " +
            "COALESCE(c.process, m.process) process, COALESCE(c.IE, m.IE) IE, " +
            "COALESCE(c.ic_name, m.ic_name) ic_name, COALESCE(c.DFF, m.DFF) DFF, " +
            "COALESCE(c.psc_sheet, m.psc_sheet) psc_sheet, COALESCE(c.device_config, m.device_config) device_config, " +
            "m.state, COALESCE(c.dept_name, m.dept_name) dept_name, COALESCE(c.class_name, m.class_name) class_name, " +
            "COALESCE(c.floor, m.floor) floor, COALESCE(c.bu, m.bu) BU " +
            "FROM mpn m " +
            "LEFT JOIN ct c ON m.MPN_id = c.MPN_id " +
            "<choose>" +
            "    <when test=\"timeStar != null and timeStar != '' and timeEnd != null and timeEnd != ''\">" +
            "        AND enter_data BETWEEN #{timeStar} AND #{timeEnd}" +
            "    </when>" +
            "    <otherwise>" +
            "        AND enter_data = CURDATE()" +
            "    </otherwise>" +
            "</choose>" +
            "WHERE m.state=1  and c.CT_id is null " +
            "<if test=\"line != null and line.size() > 0\">" +
            "    AND m.line_id IN " +
            "    <foreach item='item' collection='line' open='(' separator=',' close=')'>" +
            "        #{item}" +
            "    </foreach>" +
            "</if>" +
            "<if test=\"mpnName != null and mpnName != ''\">" +
            "    AND m.MPN_name LIKE CONCAT('%', #{mpnName}, '%')" +
            "</if>" +
            "<if test=\"ie != null and ie != ''\">" +
            "    AND m.IE LIKE CONCAT('%', #{ie}, '%')" +
            "</if>" +
            "</script>")
    List<MPNCTDTO> getCTTableByLineIdEnterDateMPNNameHaveData(
            @Param("line") List<Integer> line,
            @Param("timeStar") String timeStar,
            @Param("timeEnd") String timeEnd,
            @Param("mpnName") String mpnName,
            @Param("ie") String ie);

    @ResultMap(value = "MPNCTResultMap")
    @Select("<script>" +
            "SELECT m.MPN_id, m.MPN_name, m.internal_name, project_name, m.process_name, m.line_id, " +
            "COALESCE(c.CT_id, '') CT_id, COALESCE(c.benchmark_value, m.benchmark_value) benchmark_value, " +
            "COALESCE(enter_data, '') enter_data, COALESCE(test_values, '') test_values, " +
            "COALESCE(exception_cause, '') exception_cause, COALESCE(countermeasure, '') countermeasure, " +
            "COALESCE(acceptance, '') acceptance, COALESCE(NPI, '') NPI, " +
            "COALESCE(c.process, m.process) process, COALESCE(c.IE, m.IE) IE, " +
            "COALESCE(c.ic_name, m.ic_name) ic_name, COALESCE(c.DFF, m.DFF) DFF, " +
            "COALESCE(c.psc_sheet, m.psc_sheet) psc_sheet, COALESCE(c.device_config, m.device_config) device_config, " +
            "m.state, COALESCE(c.dept_name, m.dept_name) dept_name, COALESCE(c.class_name, m.class_name) class_name, " +
            "COALESCE(c.floor, m.floor) floor, COALESCE(c.bu, m.bu) BU " +
            "FROM mpn m " +
            "LEFT JOIN ct c ON m.MPN_id = c.MPN_id " +
            "WHERE 1=1" +
            "<if test=\"ctId != null and ctId != ''\">" +
            "    AND c.CT_Id = #{ctId}" +
            "</if>" +
            "</script>")
    List<MPNCTDTO> getCTTableByCTId(@Param("ctId") Long ctId);
}
